-- 调度脚本： 只需要执行当前脚本，即可对目标库中的对象进行检查，确认是否需要更新
-- =============================================================================

-- 设置应用名称
set application_name = 'checkMe';
set statement_timeout = 60000;


-- 1. Oracle 视图
\i Oracle_Views.sql

-- 2. Oracle 管理包
\i Oracle_Packages.sql

-- 3. Oracle 内置函数
\i Oracle_Functions.sql

-- 4. MySQL 内置函数
\i MySQL_Functions.sql

-- 5. DB5 内置函数
\i DB2_Functions.sql


-- 显示检查结果
do $RESULT_SUMMARY$
declare
    l_obj_info      record;
begin
    set client_min_messages='notice';

    raise notice '';
    raise notice '-- =====================================================================';
    raise notice '-- Compat objects need update:';
    raise notice '-- =====================================================================';
    for l_obj_info in select '   |' || pad_char
                          || rpad(coalesce(object_type, ' '), max_object_type, pad_char) || pad_char || '|' || pad_char
                          || rpad(coalesce(object_name, ' '), max_object_name, pad_char) || pad_char || '|' || pad_char
                          || rpad(coalesce(object_version, ' '), max_object_version, pad_char) || pad_char || '|' || pad_char
                          || rpad(coalesce(object_language, ' '), max_object_language, pad_char) || pad_char || '|' || pad_char
                          || rpad(coalesce(operation, ' '), max_operation, pad_char) || pad_char || '|' as result_data
                           , count(1) over() as row_cnt
                        from (select greatest(max(length(object_type)), 5) max_object_type
                                   , greatest(max(length(object_name)), 6) max_object_name
                                   , greatest(max(length(case when local_version = script_version then local_version else local_version || ' => ' || script_version end)), 7) max_object_version
                                   , greatest(max(length(case when local_language = script_language then local_language else local_language || ' => ' || script_language end)), 8) max_object_language
                                   , greatest(max(length(operation)), 9) max_operation
                                from temp_result
                               where operation not like 'Skip%'
                             ) l
                        join (select 'type' as object_type
                                   , 'name' as object_name
                                   , 'version' as object_version
                                   , 'language' as object_language
                                   , 'operation' as operation
                                   , ' ' as pad_char
                               union all
                              select '-' as object_type
                                   , '-' as object_name
                                   , '-' as object_version
                                   , '-' as object_language
                                   , '-' as operation
                                   , '-' as pad_char
                               union all
                              select object_type, object_name
                                   , case when local_version is null or local_version = script_version then script_version
                                          else local_version || ' => ' || script_version end as object_version
                                   , case when local_language is null or local_language = script_language then script_language
                                          else local_language || ' => ' || script_language end as object_language
                                   , operation, ' ' from temp_result
                               where operation not like 'Skip%'
                             ) r
                          on 1 = 1
    loop
        if l_obj_info.row_cnt = 2
        then
            raise notice '-- << ALL OBJECTS ARE UP-TO-DATE >>';
            exit;
        else
            raise notice '%', l_obj_info.result_data;
        end if;
    end loop;
end;
$RESULT_SUMMARY$ language plpgsql;

\q
